package itcast.dao.impl;

import itcast.dao.DakaDao;
import itcast.domain.Daka;
import itcast.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class DakaDaoImpl implements DakaDao {
    /**
     * 申明JdbcTemplate类,出入连接池
     */
    private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());

    //网数据库添加数据

    @Override
    public void insertdaka(Daka daka) {
        String sql="select student_id from student where student_username=?";
        int student_id=template.queryForObject(sql,Integer.class,daka.getStudent_username());
        System.out.println(student_id);
        String sql1="insert into daka(daka_studentid,daka_healthy,daka_temper,daka_remark,daka_date)" +
                "values(?,?,?,?,?)";
        String sql2="update daka set daka_state='已打卡'";

        template.update(sql1,student_id,daka.getDaka_healthy(),daka.getDaka_temper(),daka.getDaka_remark(),daka.getDaka_date());
        template.update(sql2);
    }
    //查找打卡数据

    @Override
    public List<Daka> finddakadata() {
        String sql="select daka_id,daka_date,daka_healthy,daka_temper,daka_remark,daka_state,student_username," +
                "student_name,build_name,drom_name from daka,student,build,drom where " +
                "daka_studentid=student_id and student_domitoryid=drom_id and drom_buildid=build_id and daka_state='已打卡'";
        //query返回list集合，BeanPropertyRowMapper指定字节码
        List<Daka> dakas=template.query(sql,new BeanPropertyRowMapper<Daka>(Daka.class));
    return dakas;
    }

    //未打卡数据

    @Override
    public List<Daka> findfaildaka() {
       String sql="select student_username,student_name,build_name,drom_name " +
               "from student,build,drom where student_domitoryid=drom_id " +
               "and drom_buildid=build_id " +
               "AND student.Student_ID NOT in " +
               "(SELECT student_id FROM student,daka WHERE daka.daka_studentid=student.Student_ID)";
        //query返回list集合，BeanPropertyRowMapper指定字节码
        List<Daka> dakas=template.query(sql,new BeanPropertyRowMapper<Daka>(Daka.class));

        return dakas;
    }
    //根据daka_id删除数据

    @Override
    public void deldakabyID(int daka_id) {
        String sql="delete from daka where daka_id=?";
        template.update(sql,daka_id);
    }
    //学生查找自身打卡数据


    @Override
    public Daka studentfinddaka(String id) {
        String sql="select daka_id,daka_date,daka_healthy,daka_temper,daka_remark,daka_state,student_username, student_name,build_name,drom_name " +
                "from daka,student,build,drom " +
                "where daka_studentid=student_id and student_domitoryid=drom_id and drom_buildid=build_id " +
                "and student_username=?";
        return template.queryForObject(sql,new BeanPropertyRowMapper<Daka>(Daka.class),id);
    }
}
